package com.limao.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.limao.CacheManager.CrowdSmallCompanyCacheManager;
import com.limao.CacheManager.CrowdUserAuthCache;
import com.limao.DBDataClass.CrowdSmallCompanyClass;
import com.limao.DbConnection.DbConnectionManager;
import com.limao.Tool.CacheClass;


public class CrowdSmallCompanyProvider {

	private static class DB_order_ProviderContainer {
		private static CrowdSmallCompanyProvider instance = new CrowdSmallCompanyProvider();
	}

	public static CrowdSmallCompanyProvider getInstance() {
		return DB_order_ProviderContainer.instance;
	}
	
	private CrowdSmallCompanyProvider() {

	}
	
	private CrowdSmallCompanyClass encapOrderData(ResultSet rs) throws Exception {

		CrowdSmallCompanyClass orderData = new CrowdSmallCompanyClass();
		
		orderData.smallcompanyid= rs.getString("smallcompanyid");
		orderData.smallcompanyname= rs.getString("smallcompanyname");
		orderData.state= rs.getString("state");
		orderData.regular_rate= rs.getString("regular_rate");
		orderData.totalrmb= rs.getString("totalrmb");
		orderData.withdrawrmb= rs.getString("withdrawrmb");
		orderData.ctime= rs.getString("ctime");
		orderData.utime= rs.getString("utime");
		orderData.userid= rs.getString("userid");
		orderData.marketparentid= rs.getString("marketparentid");
		orderData.pid0= rs.getString("pid0");
		orderData.pid1= rs.getString("pid1");
		orderData.level= rs.getString("level");
		orderData.mobile= rs.getString("mobile");
		
		return orderData;
	}
	
	
	public String loadSmallCompanyUserid() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String result="";
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select GROUP_CONCAT(userid) as userstr from cd_sys_smallcompany where userid is not null");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result=rs.getString("userstr");
			}
		} catch (Exception e) {
			result=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return result;

	}
	
	public CrowdSmallCompanyClass loadSmallCompanyByid(int smallcompanyid) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		CrowdSmallCompanyClass tmp=null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select s.*,a.mobile from cd_sys_smallcompany s" +
					" left join cd_user_auth a on a.userid=s.userid" +
					" where s.smallcompanyid=?");
			pstmt.setInt(1, smallcompanyid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				tmp=encapOrderData(rs);
			}
		} catch (Exception e) {
			tmp=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return tmp;

	}
	
	public List<CrowdSmallCompanyClass> loadSmallCompanyListBysmallcompanyid(int smallcompanyid) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<CrowdSmallCompanyClass> list=new ArrayList<CrowdSmallCompanyClass>();
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select s.*,a.mobile from cd_sys_smallcompany s" +
					" left join cd_user_auth a on a.userid=s.userid" +
					" where s.pid0=?");
			pstmt.setInt(1, smallcompanyid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}
		} catch (Exception e) {
			list.clear();
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return list;

	}
	
	
	//是否是办事处管理员
	public int loadSmallCompanyByuserid(String userid) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int smallcompanyid=0;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select smallcompanyid from cd_sys_smallcompany where userid=?");
			pstmt.setInt(1, CacheClass.getIntValue(userid));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				smallcompanyid=rs.getInt("smallcompanyid");
			}
		} catch (Exception e) {
			smallcompanyid=0;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return smallcompanyid;

	}
	
	//添加办事处
	public boolean addSmallCompany(String smallcompanyname,String toUserid,String regular_rate,int marketparentid,int pid0,int pid1,int level)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			long t=CacheClass.getTime();
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("insert into cd_sys_smallcompany(smallcompanyname,state,regular_rate,totalrmb,withdrawrmb,ctime,userid," +
					" marketparentid,pid0,pid1,level) values (?,?,?,?,?,?,?,?,?,?,?);");
			pstmt.setString(1, smallcompanyname);
			pstmt.setInt(2, 1);
			pstmt.setInt(3, CacheClass.getIntValue(regular_rate));
			pstmt.setBigDecimal(4, CacheClass.toBigDecimal("0"));
			pstmt.setBigDecimal(5, CacheClass.toBigDecimal("0"));
			pstmt.setLong(6, t);
			pstmt.setInt(7, CacheClass.getIntValue(toUserid));
			pstmt.setInt(8, marketparentid);
			pstmt.setInt(9, pid0);
			pstmt.setInt(10, pid1);
			pstmt.setInt(11, level);
			
			pstmt.execute();
			
			int smallcompanyid=0;
			//取得刚添加的
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select smallcompanyid from cd_sys_smallcompany where userid=? and ctime=?;");
			pstmt.setInt(1, CacheClass.getIntValue(toUserid));
			pstmt.setLong(2,t);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				smallcompanyid=rs.getInt("smallcompanyid");
			}
			
			
			//更新绑定用户的 所属办事处
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_auth set iscompanyid=? where userid=? ;");
			pstmt.setInt(1, smallcompanyid);
			pstmt.setInt(2, CacheClass.getIntValue(toUserid));
			pstmt.executeUpdate();
			
			CrowdUserAuthCache.getInstance().ClearObjectCache(toUserid);
			
		
			flag=true;
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return flag;
		
	}
	
	public boolean editSmallCompany(String smallcompanyid,String smallcompanyname,String regular_rate) 
    {	
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("update cd_sys_smallcompany set smallcompanyname=?,regular_rate=? where smallcompanyid=? ;");
			pstmt.setString(1, smallcompanyname);
			pstmt.setInt(2, CacheClass.getIntValue(regular_rate));
			pstmt.setInt(3, CacheClass.getIntValue(smallcompanyid));
			pstmt.executeUpdate();
			flag=true;
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		if(flag)
		{
			CrowdSmallCompanyCacheManager.getInstance().ClearObjectCache2(smallcompanyid);
		}
		return flag;
		
	}
	
}
